clear all
set more off, perm
local mydir "`1'"
global Tables 	`mydir'/TS/Tables
global Work   	`mydir'/TS/DataWork
global Raw    	`mydir'/TS/DataRaw
global WRDS  	`mydir'/WRDS
/* ********************************************************************* */
* [1] Get data on Index
/* ********************************************************************* */
import sas caldt totval spindx vwretd  using $WRDS/CRSP/msp500.sas7bdat, clear

rename caldt date
format date %td

* deal with weekends
tsset date
tsfill

foreach var of varlist totval spindx {
	replace `var'=L1.`var' if `var'==.
}
save $Work/crsp_sp500, replace
/* ********************************************************************* */
* [2] Get CRSP daily data on returns for stocks in S&P
/* ********************************************************************* */
import sas using $WRDS/CRSP/dsf.sas7bdat, clear case(lower)

keep permco permno date shrout cfacshr prc 
keep if year(date)>1962
replace  prc=abs(prc)
generate mcap=shrout*prc
/* ******************** */
tempfile tmp
save "`tmp'", replace

* restrict to members of S&P 
import sas using $WRDS/CRSP/dsp500list.sas7bdat, clear case(lower)
joinby permno using "`tmp'"

keep if date>=start & date<=ending
/* ******************** */
* add name
tempfile tmp
save "`tmp'", replace
import sas using $WRDS/CRSP/stocknames.sas7bdat, clear case(lower)
joinby permno using "`tmp'"
keep if date>=namedt & date<=nameenddt 

keep comnam permco permno ticker date shrout cfacshr prc mcap start ending

* a few observations are identical except for the company name (separete records for long name and abbreviated name)
duplicates drop date permno permco mcap, force
/* ******************** */
* deal with weekends
egen id=group(permco permno)
tsset id date
tsfill

foreach var of varlist permco permno shrout cfacshr prc mcap start ending {
	replace `var'=L1.`var' if `var'==.
}
foreach var of varlist comnam ticker {
	by id:replace `var'=`var'[_n-1] if missing(`var')
}
drop id
rename ticker tickercrsp

save $Work/crsp500.dta, replace
/* ****************************** */
* [3] Read IBES data
/* ****************************** */
use $Work/cs01_eps, clear
rename fpedats fpedatse

* add forecasts for dividends
merge 1:1 permco permno ticker statpers fpi using $Work/cs01_div
rename fpedats fpedatsd
drop _merge fpi_ibes

order ticker oftic cname cusip permco permno statpers fpi feps mfeps fdps fpedats*

reshape wide feps mfeps fdps fpedats* augest, i(ticker oftic cname cusip permco permno statpers) j(fpi)

* add forecasts for PTG
merge 1:1 permco permno ticker statpers using $Work/cs01_ptg
drop if _merge==2

drop _merge
save $Work/cs01, replace

* Get data only for LTG
import sas using $WRDS/IBES/March2021/statsumu_epsus.sas7bdat, case(lower) clear

keep if index(fpi,"0")>0  
rename medest LTG
keep ticker statpers LTG

merge 1:1 ticker statpers using $Work/cs01
drop _merge
* ****************************************
* restrict IBES sample to members of S&P 
tempfile cs01
save "`cs01'", replace

* restrict to members of S&P 
import sas using $WRDS/CRSP/dsp500list.sas7bdat, clear case(lower)
joinby permno using "`cs01'"

keep if statpers>=start & statpers<=ending
drop start ending
count

order ticker oftic cname cusip permco permno statpers fpedats* feps* mfeps* fdps*
duplicates tag permco permno statpers, gen(mf)
tabulate mf

drop if mf==1 & ticker=="ASC9"
drop mf
save $Work/cs01, replace
/* ********************************************************************* */
* add earnings t0 from ibes
import sas using $WRDS/IBES/March2021/actpsumu_epsus.sas7bdat, clear case(lower)
keep if (fy0a<. & statpers>=fy0edats & index(measure, "EPS")>0) 

keep ticker statpers fy*
merge 1:m ticker statpers using $Work/cs01
drop if _merge==1
drop _merge

* add earnings t0 from compustat
gen datem=mofd(statpers)
format datem %tm

merge 1:1 permco permno datem using $Work/cs01_earnings_compustat.dta, keepusing(permco permno datem niLTM0 ibLTM0)
drop if _merge==2
drop _merge datem
/* ********************************* */
* split-adjust ibes data; add market cap
gen date=statpers
format date %td

merge 1:1 permco permno date using $Work/crsp500.dta, keepusing(cfacshr shrout mcap prc)
drop if _merge==2
drop _merge 
/* ********************************* */
* add data for the index (total market cap and level of the index)
merge m:1 date using $Work/crsp_sp500.dta, keepusing(totval spindx)
drop if _merge==2
***********************************
gen fwdep =feps1/medptg if feps1>0
gen fwdep2=feps2/medptg if feps1>0

gen expret=medptg/prc-1
label var fwdep   "E[eps(t+1)/p(t+1) | t]" 
label var fwdep2  "E[eps(t+2)/p(t+1) | t]" 
label var expret  "E[p(t+1)/p(t) | t]" 
* **********************************
generate divisor=totval/spindx
gen FY0A=fy0a/cfacshr

gen levelE1=mfeps1*shrout

forvalues x=1(1)5  {
	generate fcastE`x'=mfeps`x'*shrout/divisor
	generate fcastD`x'= fdps`x'*shrout/divisor
	generate fcastP`x'=medptg*shrout/divisor

	replace  feps`x'=feps`x'/cfacshr
	replace mfeps`x'=mfeps`x'/cfacshr
	replace  fdps`x'=fdps`x'/cfacshr
	replace   medptg=medptg/cfacshr
}
gen nips0=1000*niLTM0/(shrout*cfacshr)
gen ibps0=1000*ibLTM0/(shrout*cfacshr)

drop _merge cfacshr divisor fy0a
/* ********************************* */
save $Work/sp_ibes.dta, replace
/* ********************************* */
* Create Nagel-weighted forecasts for LTG
sort statpers
by statpers: egen LEV=total(levelE1) 		if levelE1>0 & (levelE1+LTG)<.
generate LTG_Nagel=LTG*(levelE1/LEV) 		if levelE1>0
drop LEV levelE1

* Create value-weighted forecasts for earnings growth
by statpers: egen tot=total(mcap) if ln(feps1)+ln(ibps0)<.
generate vwg1=(mcap/tot)*(ln(feps1)-ln(ibps0))
drop tot

forvalues x=2(1)5  {
	local j=`x'-1
	by statpers: egen tot=total(mcap) if ln(feps`x')+ln(feps`j')<.
	generate vwg`x'=(mcap/tot)*(ln(feps`x')-ln(feps`j'))
	drop tot
}
* Create value-weighted forecasts for LTG
foreach var of varlist expret LTG fwdep* {
	by statpers: egen tot=total(mcap) if `var'<.
	replace `var'=(mcap/tot)*`var'
	drop tot
}

forvalues x=1(1)5  {
	generate  mcapE`x'=mcap if fcastE`x'<.
	generate  mcapD`x'=mcap if fcastD`x'<.
}

collapse (sum) mcapE* mcapD* fcastE* fcastD* expret LTG LTG_Nagel fwdep* vwg* (lastnm) totval, by(statpers)
pwcorr LTG* if statpers>mdy(12,1,1981)

foreach var of varlist expret fwdep* LTG LTG_Nagel vwg* mcap* {
	replace `var'=. if `var'==0
}
pwcorr LTG* if statpers>mdy(12,1,1981)

forvalues x=1(1)5 {
	generate fcastEOM`x'=fcastE`x'*(totval/mcapE`x')  /* scale up forecast so that it proxies for the entire market */
	generate fcastDOM`x'=fcastD`x'*(totval/mcapD`x')  /* scale up forecast so that it proxies for the entire market */

	replace  fcastE`x'=. if mcapE`x'/totval<.9        /* scale up forecast so that it proxies for the entire market but only if sample with forecasts is large is representative*/
	replace  fcastE`x'=fcastE`x'*(totval/mcapE`x')

	replace  fcastD`x'=. if mcapD`x'/totval<.9	/* scale up forecast so that it proxies for the entire market but only if sample with forecasts is large is representative*/
	replace  fcastD`x'=fcastD`x'*(totval/mcapD`x')
}
drop mcap* totval
gen date=statpers
format date %td

gen datem=mofd(date)
format datem %tm

save $Work/cs01_tot, replace
* ********************************************************
